﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace XinYue.Permission.Core
{
    public class DataTableHelper
    {
        #region 返回一个表的对象
        /// <summary>
        /// 返回一个表的对象
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dataTable"></param>
        /// <returns></returns>
        public static List<T> DataTableToList<T>(DataTable dataTable)
        {
            //创建T集合
            List<T> list = new List<T>();
            //循环表格的行数
            for (int i = 0; i < dataTable.Rows.Count; i++)
            {
                //创建一个实例
                T t = Activator.CreateInstance<T>();
                //获取T所有属性
                PropertyInfo[] propertyInfos = t.GetType().GetProperties();
                //循环每一列
                for (int j = 0; j < dataTable.Columns.Count; j++)
                {
                    foreach (PropertyInfo propertyInfo in propertyInfos)
                    {
                        //找到对应的属性
                        if (propertyInfo.Name.ToLower() == dataTable.Columns[j].ColumnName.ToLower())
                        {
                            if (dataTable.Rows[i][j] != DBNull.Value)
                            {
                                propertyInfo.SetValue(t, dataTable.Rows[i][j]);
                            }
                            else
                            {
                                propertyInfo.SetValue(t, null);
                            }
                        }
                    }
                }
                //加到集合里
                list.Add(t);
            }
            return list;
        }
        #endregion
        #region 根据DataTable,生成insert into语句
        /// <summary>
        /// 根据DataTable,生成insert into语句
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dataTable"></param>
        /// <param name="name"></param>
        /// <returns></returns>
        public static string DataTable<T>(DataTable dataTable, string name)
        {
            StringBuilder stringBuilder = new StringBuilder();
            //头部
            StringBuilder tou = new StringBuilder("SET IDENTITY_INSERT " + name + " ON\r\n");
            //尾部
            StringBuilder wei = new StringBuilder("SET IDENTITY_INSERT " + name + " OFF\r\n");
            for (int i = 0; i < dataTable.Rows.Count; i++)
            {
                StringBuilder sb = new StringBuilder("insert into " + name + "(");
                StringBuilder head = new StringBuilder();
                StringBuilder values = new StringBuilder();
                for (int j = 0; j < dataTable.Columns.Count; j++)
                {
                    //如果循环到最后一个列名则不用加“ ，”
                    if (j == dataTable.Columns.Count - 1)
                    {
                        head.Append(dataTable.Columns[j].ColumnName);
                    }
                    else
                    {
                        head.Append(dataTable.Columns[j].ColumnName + ",");
                    }
                    //如果是String或者DateTime类型需加单引号“‘’”
                    if (dataTable.Columns[j].DataType.ToString() == "System.String" || dataTable.Columns[j].DataType.ToString() == "System.DateTime")
                    {
                        //如果循环到最后一个参数变量则不用加“ ，”
                        if (j == dataTable.Columns.Count - 1)
                        {
                            values.Append("'" + dataTable.Rows[i][j] + "'");
                        }
                        else
                        {
                            values.Append("'" + dataTable.Rows[i][j] + "'" + ",");
                        }
                    }
                    //是int类型则不用加单引号“‘’”
                    else
                    {
                        if (j == dataTable.Columns.Count - 1)
                        {
                            values.Append(dataTable.Rows[i][j]);
                        }
                        else
                        {
                            values.Append(dataTable.Rows[i][j] + ",");
                        }
                    }
                }
                //head.Remove(head.Length - 1, sb.Length);
                //values.Remove(values.Length - 1, values.Length);
                stringBuilder.Append(sb.Append(head).Append(") VALUES (").Append(values).Append(");\r\n"));
            }
            //将头部主体尾部一起拼接然后返回
            tou.Append(stringBuilder).Append(wei);
            return tou.ToString();
        }
        #endregion
    }
}
